const {exec, escape} = require("../db/mysql")

// 获取所有学员信息
const getStudentList = async () => {
  const sql = `select *
               from student;`
  return await exec(sql)
}
// 获取学员信息
const getStudentInformation = async (identify) => {
  identify = escape(identify)
  const sql = `select *
               from student
               where identify = ${identify}`
  return await exec(sql).then(rows => {
    return rows[0] || {}
  })
}
// 更新学员信息
const updateStudentInformation = async ({identify, address, phone, issuance_date}) => {
  identify = escape(identify)
  address = escape(address)
  phone = escape(phone)
  issuance_date = escape(issuance_date)
  let sql = `update student
               set address = ${address},
                   phone   = ${phone}`
  if (issuance_date !== 'NULL') {
    sql += `, issuance_date = ${issuance_date}`
  }
  sql += ` where identify = ${identify}`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}

// 获取教练带领的学员
const getCoachStudentListById = async (id) => {
  id = escape(id)
  const sql = `select id,
                      identify,
                      name,
                      sex,
                      address,
                      phone,
                      model,
                      issuance_date
               from student
               where coach_id = ${id} and isdel = 0;`
  return await exec(sql)
}
// 添加学员
const newStudent = async ({identify, name, sex, address, phone, model, coach_name, coach_id}) => {
  identify = escape(identify)
  name = escape(name)
  sex = escape(sex)
  address = escape(address)
  phone = escape(phone)
  model = escape(model)
  coach_name = escape(coach_name)
  coach_id = escape(coach_id)
  const sql = `insert into student(identify, name, sex, address, phone, model, coach_name, coach_id, isdel)
               values (${identify}, ${name}, ${sex}, ${address}, ${phone}, ${model}, ${coach_name}, ${coach_id}, 0)`
  return await exec(sql).then(data => {
    return {id: data.insertId}
  })
}
// 根据编号删除学员
const delStudent = async (id) => {
  id = escape(id)
  const sql = `update student
               set isdel = 1
               where id = ${id}`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}
// 根据身份证号删除学员(管理员)
const delStudentByUsername = async (username) => {
  username = escape(username)
  const sql = `update student
               set isdel = 1
               where identify = ${username}`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}

module.exports = {
  getStudentInformation,
  updateStudentInformation,
  getStudentList,
  getCoachStudentListById,
  newStudent,
  delStudent,

  delStudentByUsername
}
